﻿using System;
using System.Configuration;
using System.Collections.Generic;
using System.Web.Services;
using System.Data; 
using System.Web;
using System.Linq; 
using System.Diagnostics;
using System.IO;
using System.Text;
using System.Text.RegularExpressions;
using System.Globalization;
using System.Web.UI;
using System.Web.UI.WebControls;
using Sharp; 
using Npgsql; 
using NpgsqlTypes; 

namespace Sharp
{

    
	public partial class DALBuilder : Handler 
	{ 
		private String connstr {get;set;}
		
		[HttpHandler("DALBuilder", HttpHandler.ContentType.HTML, HttpHandler.HTTPVerb.ALL)]
        public void Builder()  
        {
            string AssemblyDirectory = PreApplicationStartCode.AssemblyDirectory;

            
            try
            {
                connstr = ConfigurationManager.ConnectionStrings["connstr"].ConnectionString;
            }
            catch (Exception e)
            {
                throw new Exception("Please put a connection string in the web.config called connstr. eg" + @"
                <connectionStrings>
                    <add name=""connstr"" connectionString=""Data Source=localhost;Integrated Security=True;Initial Catalog=YourDBname""/>
                </connectionStrings>");
            }

            using (NpgsqlConnection conn = new NpgsqlConnection(connstr))
            {  
                // Parse the coonectionstring to a dictionary
                // String dbname = connstr.Split(";").Cast<String>().ToDictionary(x => x.Split("=")[0].ToLower(), x => x.Split("=")[1])["database"]; 

                string folder = Path.GetFullPath(AssemblyDirectory + "../../"); 

                if (File.Exists(folder + "dal.cs"))
                {
                    if (File.Exists(folder + "dal.cs.bak"))
                        File.Delete(folder + "dal.cs.bak");
                    File.Copy(folder + "dal.cs", folder + "dal.cs.bak");
                }

                if (HttpContext.Current != null)
                {
                    HttpContext context = HttpContext.Current;
                    context.Response.Clear();
                    context.Response.ContentType = "text/html";
                    context.Response.Buffer = false;

                    context.Response.Write("new file dal.cs is in the project folder root. add it to the project to compile. now browse to generate the object <br /><b>Procs</b> <br /> <br />");

                    context.Response.Write(folder);
                }

                StringBuilder sb = new StringBuilder();
                //TextWriter tw = new StreamWriter(context.Server.MapPath("/dal.cs"));
                
					//build list of datatypes


                    GenCode(sb, conn); 
                    File.WriteAllText(folder + "dal.cs", sb.ToString()); 
            } 
        }

        public String ConvertToCLR(NpgsqlDbType sqltype)
		{ 
            switch (sqltype)
            {
				case NpgsqlDbType.Bigint		: return "Int64";
				case NpgsqlDbType.Boolean    	: return "Boolean";
				case NpgsqlDbType.Bytea      	: return "Byte[]";
				case NpgsqlDbType.Date       	: return "DateTime";
				case NpgsqlDbType.Double     	: return "Double";
				case NpgsqlDbType.Integer    	: return "Int32";
				case NpgsqlDbType.Money      	: return "Decimal";
				case NpgsqlDbType.Numeric    	: return "Decimal";
				case NpgsqlDbType.Real       	: return "Single";
				case NpgsqlDbType.Smallint   	: return "Int16";
				case NpgsqlDbType.Text       	: return "String";
				case NpgsqlDbType.Time       	: return "DateTime"; 
				case NpgsqlDbType.Timestamp  	: return "DateTime";
				case NpgsqlDbType.TimestampTZ	: return "DateTime"; 
				case NpgsqlDbType.Varchar    	: return "String";
				case NpgsqlDbType.Inet       	: return "IPAddress";
				case NpgsqlDbType.Bit        	: return "Boolean"; 
                default: return "object";
            } 
        } 

		public String ConvertToCLR(System.Data.DbType sqltype)
        { 
            switch (sqltype)
            {
				case System.Data.DbType.Int64    : return "Int64";
				case System.Data.DbType.Boolean  : return "Boolean";
				case System.Data.DbType.Binary   : return "Byte[]";
				case System.Data.DbType.Date     : return "DateTime";
				case System.Data.DbType.Double   : return "Double";
				case System.Data.DbType.Int32    : return "Int32"; 
				case System.Data.DbType.Decimal  : return "Decimal";
				case System.Data.DbType.Single   : return "Single";
				case System.Data.DbType.Int16    : return "Int16";
				case System.Data.DbType.String   : return "String"; 
				case System.Data.DbType.Time     : return "DateTime"; 
				case System.Data.DbType.DateTime : return "DateTime"; 
                default: return "object";
            } 
        }


		public class SQLFunction
		{
			public String Name { get; set; }
			public List<StoredProcedureParameter> Parameters { get; set; }
		}

		public class StoredProcedureParameter
		{
			public String Name { get; set; }
			public Boolean IsOutputParameter { get; set; }
			public System.Data.DbType DataType { get; set; }
		}

		public String GetNameFromOID (string oid)
		{
			using (NpgsqlConnection conn = new NpgsqlConnection(connstr)) { 
				using(NpgsqlCommand cmd = new NpgsqlCommand(@"Select typname from pg_type where oid = " + oid, conn))
				{
					conn.Open();
					var dr = cmd.ExecuteReader();
					if (dr.Read ())
						return dr[0].ToString();
				}
			} 
			return "void";
		}

		public System.Data.DbType GetTypeFromOID (string oid)
		{
			return GetTypeFromName(GetNameFromOID(oid));
		}

		public System.Data.DbType GetTypeFromName (string name)
		{
			switch (name)
			{
			case "int8" : return System.Data.DbType.Int64;    //int8       
			case "bool" : return System.Data.DbType.Boolean;  //bool       
			case "bytea" : return System.Data.DbType.Binary;   //bytea      
			case "date" : return System.Data.DbType.Date;     //date       
			case "float8" : return System.Data.DbType.Double;   //float8     
			case "int4" : return System.Data.DbType.Int32;    //int4       
			case "money" : return System.Data.DbType.Decimal;  //money      
			case "numeric" : return System.Data.DbType.Decimal;  //numeric    
			case "float4" : return System.Data.DbType.Single;   //float4     
			case "int2" : return System.Data.DbType.Int16;    //int2       
			case "text" : return System.Data.DbType.String;   //text       
			case "time" : return System.Data.DbType.Time;     //time       
			case "timetz" : return System.Data.DbType.Time;     //timetz     
			case "timestamp" : return System.Data.DbType.DateTime; //timestamp  
			case "timestamptz" : return System.Data.DbType.DateTime; //timestamptz 
			case "varchar" : return System.Data.DbType.String;   //varchar    
			case "inet" : return System.Data.DbType.Object;   //inet       
			case "bit" : return System.Data.DbType.Boolean;  //bit         
			case "void" : return System.Data.DbType.Object;  //bit         
			default: throw new Exception(name + " is not a match for a dbtype");
            }  
		}

        public void GenCode (StringBuilder gencode, NpgsqlConnection conn)
		{
			HttpContext context = HttpContext.Current;

			gencode.Append (@"using System;
using System.Data; 
using System.Collections;
using System.Collections.Generic;
using System.Web;
using Sharp;  

// Code Auto Generated " + DateTime.Now.ToString() + @"
namespace " + "DAL.Procs" + @"
{

");
			int count = 0;
			String error = "";

			string ProcCommand = @"
select proname, pronargs, prorettype, typname, proargtypes, proallargtypes, proargmodes, proargnames  
from pg_proc p
join pg_type t on p.prorettype = t.oid
where proname like 'usp\_%'";

			List<SQLFunction> funcs = new List<SQLFunction>();

			 
			using (NpgsqlCommand command = new NpgsqlCommand(ProcCommand, conn)) 
			{ 
				conn.Open (); 

				NpgsqlDataReader dr = command.ExecuteReader ();

				while (dr.Read()) {

					SQLFunction func = new SQLFunction() {Name = dr["proname"].ToString(), Parameters = new List<StoredProcedureParameter>()};
					context.Response.Write("<br /><br />" + func.Name + ": " + dr["proallargtypes"].ToString());
					if (dr["proargnames"].ToString().NNOE())
					{
						var names = (String[])dr["proargnames"]; //.ToString().TrimStart(new char[] {'{'}).TrimEnd(new char[] {'}'}).Split(new char[] {','}, StringSplitOptions.RemoveEmptyEntries);
						var modes = dr["proargmodes"].ToString().NOE() ? new string[] {} : ((String[])dr["proargmodes"]); //.ToString().TrimStart(new char[] {'{'}).TrimEnd(new char[] {'}'}).Split(new char[] {','}, StringSplitOptions.RemoveEmptyEntries);
						var alltypes = dr["proallargtypes"].ToString().NOE() ? new Int64[] {} : ((Int64[])dr["proallargtypes"]); //.ToString().TrimStart(new char[] {'{'}).TrimEnd(new char[] {'}'}).Split(new char[] {','}, StringSplitOptions.RemoveEmptyEntries);
						var types = dr["proargtypes"].ToString().Split(new char[] {' '}, StringSplitOptions.RemoveEmptyEntries);

						bool hasModes = modes.Length > 0;
						var paramsCount = hasModes ? modes.Length : types.Length;

						for (int i = 0; i < paramsCount; i++){

							//not table field
							if (!hasModes || modes[i].LikeOne(new string[] {"i", "o"}))  
							{ 
								context.Response.Write("<br /> i = " + i + ", type = " + (hasModes ? alltypes[i].ToString() : types[i]));

								func.Parameters.Add(new StoredProcedureParameter() {
									Name = names[i],
									IsOutputParameter = hasModes ? modes[i].Like("o") : false,
									DataType = GetTypeFromOID(hasModes ? alltypes[i].ToString() :types[i])
								});  	
							}
						} 
					}
					funcs.Add(func);
				}
			}


            foreach (SQLFunction sp in funcs)
			{ 
				StringBuilder tw = new StringBuilder(); 
                     
                context.Response.Write("<br><br>" + sp.Name + "<br>");

                tw.Append("\npublic partial class " + sp.Name + "  : Sharp.DAL  \n{");

                List<StoredProcedureParameter> param = new List<StoredProcedureParameter>();


                foreach (StoredProcedureParameter par in sp.Parameters)
                {
                    param.Add(par);

                    tw.Append("\n\n\t///<summary>  "  /*par.Properties["Length"].Value.ToString()*/ + " " + (par.IsOutputParameter == true ? "output" : "") + " </summary>");
                    string isnullable = (ConvertToCLR(par.DataType) == "String" || ConvertToCLR(par.DataType) == "Byte[]" || par.Name.Contains("_id") || par.Name.Replace("param_", "").Like("id") ? "" : "?");
                    tw.Append("\n\tpublic " + ConvertToCLR(par.DataType) + isnullable + " " + par.Name.Replace("param_", "") + "{get;set;}");
                }

                tw.Append("\n");

                tw.Append("\n\n\tpublic " + sp.Name + "() {");

                tw.Append("\n\n\t\tdb = new DB(\"" + sp.Name + "\");");
                tw.Append("\n\t}");


                tw.Append("\n\n\tprivate void preExecute() {");
                foreach (StoredProcedureParameter par in param)
                {
                    if (ConvertToCLR(par.DataType) == "String")
                        tw.Append("\n\t\tif (" + par.Name.Replace("param_", "") + " == null)  db.NewParameter(\"" + par.Name.Replace("param_", "") + "\", System.Data.DbType." + par.DataType.ToString().Replace("Numeric", "Decimal").Replace("VarCharMax", "VarChar") + ", 0, DBNull.Value, " + par.IsOutputParameter.ToString().ToLower() + "); else  db.NewParameter(\"" + par.Name.Replace("param_", "") + "\", System.Data.DbType." + par.DataType.ToString().Replace("Numeric", "Decimal").Replace("VarCharMax", "VarChar") + ", 0,Encode(" + par.Name.Replace("param_", "") + "), " + par.IsOutputParameter.ToString().ToLower() + "); ");
                    else if (ConvertToCLR(par.DataType) == "Byte[]" || par.Name.Contains("_id") || par.Name.Replace("param_", "").Like("id"))
                        tw.Append("\n\t\tdb.NewParameter(\"" + par.Name.Replace("param_", "") + "\", System.Data.DbType." + par.DataType.ToString().Replace("Numeric", "Decimal").Replace("VarCharMax", "VarChar") + ", 0, " + par.Name.Replace("param_", "") + ", " + par.IsOutputParameter.ToString().ToLower() + "); ");
                    else
                        tw.Append("\n\t\tif (!" + par.Name.Replace("param_", "") + ".HasValue)  db.NewParameter(\"" + par.Name.Replace("param_", "") + "\", System.Data.DbType." + par.DataType.ToString().Replace("Numeric", "Decimal").Replace("VarCharMax", "VarChar") + ", 0, DBNull.Value, " + par.IsOutputParameter.ToString().ToLower() + "); else  db.NewParameter(\"" + par.Name.Replace("param_", "") + "\", System.Data.DbType." + par.DataType.ToString().Replace("Numeric", "Decimal").Replace("VarCharMax", "VarChar") + ", 0, " + par.Name.Replace("param_", "") + ", " + par.IsOutputParameter.ToString().ToLower() + "); ");
                }
                tw.Append("\n\t}");

                tw.Append("\n\n\tprivate void postExecute() {");
                foreach (StoredProcedureParameter par in param)
                {
                    tw.Append("\n\t\tif (base.db.cmd.Parameters[\"" + par.Name.Replace("param_", "") + "\"].Value != DBNull.Value) " + par.Name.Replace("param_", "") + " = (" + ConvertToCLR(par.DataType) + ")base.db.cmd.Parameters[\"" + par.Name.Replace("param_", "") + "\"].Value;");
                }
                tw.Append("\n\t}");

                int i = 0;

				//BUILD resultsets
                 
				using (NpgsqlCommand cmd = new NpgsqlCommand())
				{
					StringBuilder sb = new StringBuilder(); 
					foreach (StoredProcedureParameter par in param)
					{ 
						if (!par.IsOutputParameter) {
							sb.AppendFormat("null,");
						}
                            
					} 
                    cmd.Connection = conn;
                    cmd.CommandText = "select * from ( select * from " +
						sp.Name + "(" + sb.ToString().TrimEnd(new char[] {','}) + 
						")  )x where false;";
                    context.Response.Write("<pre>" + cmd.CommandText + "</pre>");

                    NpgsqlDataReader dr = cmd.ExecuteReader(); 

					if (dr.FieldCount > 0)
					{

						do
						{
							i++;
							tw.Append("\n\n\tpublic class ResultSet" + i + " \n\t{");
							StringBuilder binding = new StringBuilder();


							//DataTable dt = dr.GetSchemaTable();
							for (int f = 0; f < dr.FieldCount; f++)
							{
								var dbtypename = ConvertToCLR(GetTypeFromName(GetNameFromOID(dr.GetDataTypeOID(f))));
								if (!GetNameFromOID(dr.GetDataTypeOID(f)).LikeOne(new string[] {"record","void"})) 
								{
									bool notnullable = dbtypename.LikeOne(new string[] { "String", "Byte[]" }) || dr.GetName(f).Replace("param_", "").Like("id") || dr.GetName(f).Replace("param_", "").Like("id");

									//tw.Append("\n\t\tpublic " + ConvertToCLR((System.Data.DbType)Enum.Parse(typeof(System.Data.DbType), dr.GetDataTypeName(f), true)) + ((System.Data.DbType)Enum.Parse(typeof(System.Data.DbType), dr.GetDataTypeName(f), true) == System.Data.DbType.DateTime).ToCustom("?", "") + " " + dr.GetName(f) + " {get;set;}");
									tw.Append("\n\t\tpublic " + dbtypename + notnullable.ToCustom("", "?") + " " + dr.GetName(f) + " {get;set;} //" + dbtypename + " - " + dr.GetDataTypeName(f));
									binding.AppendFormat("\n\t\t\t\tif (dal[\"{0}\"] != DBNull.Value) rs" + i + ".{0} = ({1})dal[\"{0}\"];", dr.GetName(f), dbtypename);
								}
							}


							tw.Append("\n\n\t\tpublic static ResultSet" + i + "[] Init(" + sp.Name + " dal, StateBag sb) \n\t\t{");
							tw.Append("\n\t\t\tList<ResultSet" + i + "> rs" + i + "s = new List<ResultSet" + i + ">();");
							tw.Append("\n\t\t\twhile (dal.Read(sb))");
							tw.Append("\n\t\t\t{");
                            tw.Append("\n\t\t\t\tResultSet" + i + " rs" + i + " = new ResultSet" + i + "();");

                            tw.Append(binding.ToString());

                            tw.Append("\n\t\t\t\t\trs" + i + "s.Add(rs" + i + ");;");
                            tw.Append("\n\t\t\t}");
                            tw.Append("\n\t\t\treturn rs" + i + "s.ToArray();");

                            tw.Append("\n\t\t}");
							tw.Append("\n\t}");

						} while (dr.NextResult());
					}

                                    tw.Append(@"

    public new Boolean Execute(StateBag sb)
    {
        preExecute();
        bool flag = base.Execute(sb);
        if (flag)
        {
            //build all result sets
            ");
					for (int sets = 1; i >= sets; sets++)
					{
						tw.Append("RS" + sets + " = ResultSet" + sets + ".Init(this, sb);\n\t\t\t");
					}
					tw.Append(@" 

            postExecute();
			if (base.db.cmd.Parameters[""RetVal""].Value != DBNull.Value) Return = (int)base.db.cmd.Parameters[""RetVal""].Value;

        }
        else
        {
            //just instantiate empty result sets 
");
					for (int sets = 1; i >= sets; sets++)
					{
						tw.Append("RS" + sets + " = new ResultSet" + sets + " [] {};\n\t\t\t");
					}
					tw.Append(@" 
        }
        return flag;
    }");




                                }
                             

                            for (int sets = i; sets > 0; sets--)
                            {
                                tw.Append("public ResultSet" + sets + "[] RS" + sets + " { get; set; }");
                            }





                            tw.Append("\n} \n");

                    

                    gencode.Append(tw.ToString());

			}
                 
			
            gencode.Append("}");

            context.Response.Write(error);

		}
	}
}